package com.estore.daoImpl;

import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

import com.estore.dao.Product_Dao;
import com.estore.entity.Brand;
import com.estore.entity.Categories;
import com.estore.entity.Product;
import com.estore.info.CatInfo;
import com.estore.info.ProductInfo;
import com.estore.mapper.BrandMapper;
import com.estore.mapper.CategoriesMapper;
import com.estore.mapper.ProductInfoMapper;
import com.estore.mapper.ProductMapper;

public class Product_DaoImpl implements Product_Dao {

	private DataSource dataSource;
	private JdbcTemplate templateObject;

	@Override
	public void setDataSource(DataSource datasource) {
		this.dataSource = datasource;
		this.templateObject = new JdbcTemplate(datasource);

	}

	@Override
	public Categories getCategories(Integer catID) {
		String sql = "select * from categories where catid = ?";
		Categories cat = new Categories();
		cat = templateObject.queryForObject(sql,
				new Object[] { catID.toString() }, new CategoriesMapper());
		return cat;
	}

	public void addProduct(ProductInfo info) {
		String sql = "insert into product (productname, catid, brandid, adddate) value (?, ?, ?, ?)";
		Object[] list = new String[] { info.getProductName(),
				info.getCatID().toString(), info.getBrandID().toString(),
				info.getAddDate() };
		templateObject.update(sql, list);
		sql = "select * from product where productname = ?";
		Product product = templateObject.queryForObject(sql,
				new Object[] { info.getProductName() }, new ProductMapper());
		for (String record : info.getListLinkImg()) {
			if (record.equals(info.getListLinkImg().get(0))) {
				sql = "insert into product_image (productid, linkimg, mainimg) value (?, ?, 1)";
				templateObject.update(sql,
						new Object[] { product.getProductID(), record });
			} else {
				sql = "insert into product_image (productid, linkimg, mainimg) value (?, ?, 0)";
				templateObject.update(sql,
						new Object[] { product.getProductID(), record });
			}
		}
	}

	@Override
	public Brand getBrand(Integer brandID) {
		String sql = "select * from brand where brandid = ?";
		try {
			Brand brand = templateObject.queryForObject(sql,
					new Object[] { brandID }, new BrandMapper());
			return brand;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}

	@Override
	public List<Categories> listCategories() {
		String sql = "select * from categories where subof = 0";
		List<Categories> categories = templateObject.query(sql,
				new CategoriesMapper());
		return categories;
	}

	@Override
	public List<Categories> listSubCategories(Integer catID) {
		String sql = "select * from categories where subof = ?";
		List<Categories> categories = templateObject.query(sql,
				new Object[] { catID }, new CategoriesMapper());
		return categories;
	}

	@Override
	public List<Product> getListProduct(Integer catID) {
		String sql = "select * from product where catid = ?";
		List<Product> product = templateObject.query(sql,
				new Object[] { catID }, new ProductMapper());
		return product;
	}

	@Override
	public List<Product> getListAllProduct() {
		String sql = "select * from product";
		try {
			List<Product> product = templateObject.query(sql, new Object[] {},
					new ProductMapper());
			return product;
		} catch (Exception e) {
			System.out.println();
			return null;
		}
	}

	@Override
	public void addBrand(String brandName, String website) {
		String sql = "insert into brand (brandName, website) values (?, ?)";
		Object[] list = new String[] { brandName, website };
		templateObject.update(sql, list);

	}

	@Override
	public List<Brand> getListBrand() {
		String sql = "select * from brand";
		List<Brand> brand = templateObject.query(sql, new BrandMapper());
		return brand;
	}

	// check brandName available
	@Override
	public boolean checkBrand(String brandName) {
		String sql = "select * from brand where brandName = ?";
		Brand brand;
		try {
			brand = templateObject.queryForObject(sql,
					new Object[] { brandName }, new BrandMapper());
			return true;
		} catch (Exception e) {
			System.out.println(e);
		}
		return false;
	}

	@Override
	public void updateBrand(Integer brandID, String brandName, String website) {
		String sql = "update brand set brandname = ?, website = ? where brandID = ?";
		templateObject
				.update(sql, new Object[] { brandName, website, brandID });
	}

	@Override
	public void insertCategories(CatInfo cat) {
		String sql = "insert into categories (catname, subof) values (? , ?)";
		Object[] list = new String[] { cat.getCatName(),
				cat.getSubOf().toString() };
		try {
			templateObject.update(sql, list);
		} catch (Exception e) {
			System.out.println(e);
		}
	}

	@Override
	public boolean checkCat(String catName) {
		String sql = "select * from categories where catName = ?";
		Categories cat;
		try {
			cat = templateObject.queryForObject(sql, new Object[] { catName },
					new CategoriesMapper());
			return true;
		} catch (Exception e) {
			System.out.println(e);
		}
		return false;
	}

	@Override
	public void updateCategories(CatInfo info) {
		String sql = "update categories set catName = ?, subof = ? where catid = ?";
		Object[] list = new String[] { info.getCatName(),
				info.getSubOf().toString(), info.getCatID().toString() };
		templateObject.update(sql, list);
	}

	// check brandName available

	@Override
	public boolean checkProduct(String productName) {
		Product product;
		String sql = "select * from product where productname = ?";
		Object[] list = new String[] { productName };
		try {
			product = templateObject.queryForObject(sql, list,
					new ProductMapper());
			return true;
		} catch (Exception e) {

		}
		return false;
	}

	@Override
	public List<ProductInfo> getTop12NewProduct() {
		List<ProductInfo> listProduct;
		String sql = "SELECT product.productname, brand.brandname , categories.catname, product_image.linkimg"
				+ " FROM product"
				+ " join product_image on product.productid = product_image.productid"
				+ " join brand on product.brandid = brand.brandid"
				+ " join categories on categories.catid = product.catid"
				+ " where product_image.mainimg = 1"
				+ " order by addDate desc  LIMIT 12";
		try {
			listProduct = templateObject.query(sql, new ProductInfoMapper());
			return listProduct;
		} catch (Exception e) {
			System.out.println(e);
		}
		return null;
	}
}
